#!/usr/bin/python3
#显示本机数据上传与接收情况，并将数据写入数据库
import psutil
import pymysql
import time
import warnings

warnings.filterwarnings('ignore')
#connect the datebase ,create database and table
try:
    db = pymysql.connect(user='root',passwd='123123',host='localhost')
    cur = db.cursor()
    db.autocommit(True)
except:
    print("can't connect mysql ,please check the username or passwd")
    exit()
try:
    sql = "create database test"
    cur.execute(sql)
except:
    print("database test is exist;")
sql2 = "create table if not exists test.nic2(time_now decimal(20),packets_in decimal(20),packets_out decimal(20),bytes_in decimal(20),bytes_out decimal(20),primary key(time_now))"
cur.execute(sql2)

b1_sent, b1_recv, p1_sent, p1_recv ,*test=psutil.net_io_counters()
flag = 0
i = 0
while True:
    b2_sent, b2_recv, p2_sent, p2_recv ,*test=psutil.net_io_counters()
    if flag == 0:
        flag = 1
        time.sleep(1)
        continue

    p_recv = p2_recv - p1_recv
    p_sent = p2_sent - p1_sent
    b_recv = b2_recv - b1_recv
    b_sent = b2_sent - b1_sent
    b1_sent, b1_recv, p1_sent, p1_recv = b2_sent, b2_recv, p2_sent, p2_recv
    timestamp = int(time.time())
    if (i % 10) == 0:
        print("   time     \tbytes_recv\tbytes_sent\tpackets_recv\tpackets_sent")

    print("%d\t%d\t\t%d\t\t%d\t\t%d " %( timestamp, b_recv , b_sent, p_recv, p_sent))
    #write into database
    kk = "insert into test.nic2(time_now,packets_in,packets_out,bytes_in,bytes_out) values ( {time_now},{pa_recv},{pa_sent},{bt_recv},{bt_sent})".format( time_now=timestamp, pa_recv=p_recv, pa_sent=p_sent, bt_recv=b_recv, bt_sent=b_sent)
    cur.execute(kk)
    
    i = i + 1
    time.sleep(1)
db.close()
